package io.itit.smartjdbc.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import io.itit.smartjdbc.ResultSetHandler;
import io.itit.smartjdbc.SmartDataSource;
import io.itit.smartjdbc.SmartJdbc;
import io.itit.smartjdbc.SqlInterceptor;
import io.itit.smartjdbc.Types;
import io.itit.smartjdbc.enums.DatabaseType;
import io.itit.smartjdbc.stat.DBStat;
import io.itit.smartjdbc.util.DumpUtil;
import io.itit.smartjdbc.util.JdbcUtil;

/**
 * 
 * @author skydu
 *
 */
public abstract class BaseDAO{
	//
	private static final Logger logger=LoggerFactory.getLogger(BaseDAO.class);
	//
	protected String datasourceIndex;
	//
	public static long SLOW_SQL_MIN_USE_TIME=1000;//1s
	//
	public BaseDAO() {
		datasourceIndex=SmartDataSource.DEFAULT_DATASOURCE_INDEX;
	}
	//
	protected void beforeExcute(String sql,Object ... parameters) {
		for (SqlInterceptor interceptor : getSmartDataSource().getSqlInterceptors()) {
			interceptor.beforeExcute(sql,parameters);
		}
	}
	//
	protected void afterExcute(String sql,Object ... parameters) {
		for (SqlInterceptor interceptor : getSmartDataSource().getSqlInterceptors()) {
			interceptor.afterExcute(sql,parameters);
		}
	}
	//
	protected int executeUpdate(Connection conn,PreparedStatement ps,boolean returnAutoGeneratedKeys)
	throws SQLException{
		ResultSet rs=null;
		try {
			int rowCount = ps.executeUpdate();
			if(returnAutoGeneratedKeys){//
				int autoIncrease = -1;
				rs = ps.getGeneratedKeys();
				if (rs.next()) {
					autoIncrease = rs.getInt(1);
				}
				return autoIncrease;
			}
			return rowCount;
		} catch (SQLException e) {
			throw e;
		}finally {
			JdbcUtil.close(ps,rs);
		}
	}

	//
	protected int[] executeBatch(Connection conn,PreparedStatement ps)
	throws SQLException{
				ResultSet rs=null;
				try {
					return ps.executeBatch();
				} catch (SQLException e) {
					throw e;
				}finally {
					JdbcUtil.close(ps,rs);
				}
			}
	//
	protected int executeWithGenKey(String sql,Object ... parameters){
		return executeUpdate0(sql, true, parameters);
	}
	//
	protected int executeUpdate0(String sql,boolean returnAutoGeneratedKeys,Object ... parameters){
		int result=-1;
		long startTime=System.currentTimeMillis();
		long useTime=0;
		boolean isException=false;
		try {
			beforeExcute(sql, parameters);
			Connection conn=getConnection();
			PreparedStatement ps = conn.prepareStatement(sql.toString(),
					returnAutoGeneratedKeys ? Statement.RETURN_GENERATED_KEYS:Statement.NO_GENERATED_KEYS);
			set(conn, ps, parameters);
			result=executeUpdate(conn, ps,returnAutoGeneratedKeys);
			useTime=System.currentTimeMillis()-startTime;
			return result;
		} catch (Throwable e) {
			isException=true;
			throw new RuntimeException(e.getMessage(),e);
		}finally {
			if(logger.isDebugEnabled()){
				logger.debug("executeUpdate \nisException:{} \nuseTime:{}ms \nresult:{} \nsql:{} \nparameters:{}\n{}",
						isException,
						useTime,
						result,
						sql,
						parameters==null?0:parameters.length,
						dumpParameters(parameters));
			}
			afterExcute(sql, parameters);
			DBStat.stat(sql,useTime,isException);//stat
			if(!isException&&useTime>=SLOW_SQL_MIN_USE_TIME){
				logger.warn("executeUpdate  \nuseTime:{}ms \nresult:{} \nsql:{} \nparameters:{}\n{}",
						useTime,
						result,
						sql,
						parameters==null?0:parameters.length,
						dumpParameters(parameters));
			}
		}
	}
	//
	protected long executeLargeUpdate(Connection conn,PreparedStatement ps,boolean autoGeneratedKeys)
	throws SQLException{
		ResultSet rs=null;
		try {
			long rowCount = ps.executeLargeUpdate();
			if(autoGeneratedKeys){//
				long autoIncrease = -1;
				rs = ps.getGeneratedKeys();
				if (rs.next()) {
					autoIncrease = rs.getLong(1);
				}
				return autoIncrease;
			}
			return rowCount;
		} catch (SQLException e) {
			throw e;
		}finally {
			JdbcUtil.close(ps,rs);
		}
	}
	//
	protected long executeLargeUpdate(String sql,boolean returnAutoGeneratedKeys,Object ... parameters){
		try {
			Connection conn=getConnection();
			PreparedStatement ps = conn.prepareStatement(sql.toString(),
					returnAutoGeneratedKeys ? Statement.RETURN_GENERATED_KEYS:Statement.NO_GENERATED_KEYS);
			set(conn, ps, parameters);
			beforeExcute(sql, parameters);
			return executeLargeUpdate(conn, ps,returnAutoGeneratedKeys);
		} catch (Throwable e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage(),e);
		}finally {
			afterExcute(sql, parameters);
		}
	}
	//
	protected int[] executeBatch(String sql,List<Object[]> parameters){
		int[] result=null;
		long startTime=System.currentTimeMillis();
		long useTime=0;
		boolean isException=false;
		try {
			Connection conn=getConnection();
			PreparedStatement ps = conn.prepareStatement(sql.toString(),
					ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			if(parameters!=null){
				for (Object[] parameter : parameters) {
					set(conn, ps, parameter);
					ps.addBatch();
				}
			}
			beforeExcute(sql, parameters);
			result=executeBatch(conn,ps);
			useTime=System.currentTimeMillis()-startTime;
			return result;
		} catch (Throwable e) {
			isException=true;
			throw new RuntimeException(e.getMessage(),e);
		}finally {
			if(logger.isDebugEnabled()){
				logger.debug("executeBatch \nisException:{} \nuseTime:{}ms \nresult:{} \nsql:{} \nparameters:{}\n{}",
						isException,
						useTime,
						result,
						sql,
						parameters==null?0:parameters.size(),
						DumpUtil.dump(parameters));
			}
			afterExcute(sql, parameters);
			DBStat.stat(sql,useTime,isException);//stat
			if(!isException&&useTime>=SLOW_SQL_MIN_USE_TIME){
				logger.warn("executeBatch  \nuseTime:{}ms \nresult:{} \nsql:{} \nparameters:{}\n{}",
						useTime,
						result,
						sql,
						parameters==null?0:parameters.size(),
						DumpUtil.dump(parameters));
			}
		}
	}
	//
	public long largeInsert(String sql,boolean returnAutoGeneratedKeys,Object ... parameters){
		return executeLargeUpdate(sql, returnAutoGeneratedKeys, parameters);
	}
	//
	public int insert(String sql,boolean returnAutoGeneratedKeys,Object ... parameters){
		return executeUpdate0(sql, returnAutoGeneratedKeys, parameters);
	}
	//
	public int[] insertBatch(String sql,List<Object[]> parameters){
		return executeBatch(sql, parameters);
	}
	//
	protected int executeUpdate(String sql,Object ... parameters){
		return executeUpdate0(sql,false,parameters);
	}
	//
	protected <T> T queryForObject(String sql,ResultSetHandler<T> handler,Object ... parameters){
		T bean=null;
		boolean isException=false;
		long startTime=System.currentTimeMillis();
		long useTime=0;
		try {
			Connection conn=getConnection();
			PreparedStatement ps = conn.prepareStatement(sql.toString(),
					ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			set(conn, ps, parameters);
			beforeExcute(sql, parameters);
			bean=query(conn, ps, handler);
			useTime=System.currentTimeMillis()-startTime;
			return bean;
		} catch (Throwable e) {
			isException=true;
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage(),e);
		}finally {
			if(logger.isDebugEnabled()){
				logger.debug("query \nisException:{} \nuseTime:{}ms \nsql:{} \nparameters:{} \nresult:{}",
						isException,
						useTime,
						sql,
						dumpParameters(parameters));
			}
			afterExcute(sql, parameters);
			DBStat.stat(sql,useTime,isException);//stat
			if(!isException&&useTime>=SLOW_SQL_MIN_USE_TIME){
				logger.warn("query \nisException:{} \nuseTime:{}ms \nsql:{} \nparameters:{} \nresult:{}",
						isException,
						useTime,
						sql,
						dumpParameters(parameters));
			}
		}
	}
	//
	protected <T> List<T> queryForList(String sql,ResultSetHandler<T> handler,Object ... parameters){
		List<T> list=null;
		boolean isException=false;
		long startTime=System.currentTimeMillis();
		long useTime=0;
		try {
			Connection conn=getConnection();
			PreparedStatement ps = conn.prepareStatement(sql.toString(),
					ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			set(conn, ps, parameters);
			beforeExcute(sql, parameters);
			list=queryList(conn, ps, handler);
			useTime=System.currentTimeMillis()-startTime;
			return list;
		} catch (Throwable e) {
			isException=true;
			throw new RuntimeException(e.getMessage(),e);
		}finally {
			if(logger.isDebugEnabled()){
				logger.debug("queryList \nisException:{} \nuseTime:{}ms \nsql:{}\nparameters:{}\nresult:{}",
						isException,
						useTime,
						sql,
						dumpParameters(parameters),
						list==null?0:list.size()
						);
			}
			afterExcute(sql, parameters);
			DBStat.stat(sql,useTime,isException);//stat
			if(!isException&&useTime>=SLOW_SQL_MIN_USE_TIME){
				logger.warn("queryList \nisException:{} \nuseTime:{}ms \nsql:{}\nparameters:{}\nresult:{}",
						isException,
						useTime,
						sql,
						dumpParameters(parameters),
						list==null?0:list.size()
						);
			}
		}
	}
	
	protected String dumpParameters(Object[] parameters) {
		return DumpUtil.dump(parameters);
	}
	//
	protected <T> T query(Connection conn,PreparedStatement ps,ResultSetHandler<T> handler) 
	throws Exception{
		ResultSet rs=null;
		try {
			rs = ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					return handler.handleRow(rs); 
				}
			}
			return null;
		} finally {
			JdbcUtil.close(ps,rs);
		}
	}
	//
	protected <T> List<T> queryList(Connection conn,PreparedStatement ps,ResultSetHandler<T> handler) 
	throws Exception{
		ResultSet rs=null;
		try {
			rs = ps.executeQuery();
			List<T> list=new ArrayList<>();
			if(rs!=null){
				while(rs.next()){
					list.add(handler.handleRow(rs)); 
				}
			}
			return list;
		} finally {
			JdbcUtil.close(ps,rs);
		}
	}
	
	//
	protected Boolean queryForBoolean(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getBoolean(1), parameters);
	}
	//
	protected String queryForString(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getString(1), parameters);
	}
	//
	protected Double queryForDouble(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getDouble(1), parameters);
	}
	//
	protected Float queryForFloat(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getFloat(1), parameters);
	}
	//
	protected Integer queryForInteger(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getInt(1), parameters);
	}
	//
	protected Long queryForLong(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getLong(1), parameters);
	}
	//
	protected Short queryForShort(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getShort(1), parameters);
	}
	//
	protected BigDecimal queryForBigDecimal(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getBigDecimal(1), parameters);
	}
	//
	protected Byte queryForByte(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getByte(1), parameters);
	}
	//
	protected Date queryForDate(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getTimestamp(1), parameters);
	}
	//
	protected List<Boolean> queryForBooleans(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getBoolean(1), parameters);
	}
	//
	protected List<String> queryForStrings(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getString(1), parameters);
	}
	//
	protected List<Double> queryForDoubles(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getDouble(1), parameters);
	}
	//
	protected List<Float> queryForFloats(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getFloat(1), parameters);
	}
	//
	protected List<Integer> queryForIntegers(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getInt(1), parameters);
	}
	//
	protected List<Long> queryForLongs(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getLong(1), parameters);
	}
	//
	protected List<Short> queryForShorts(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getShort(1), parameters);	
	}
	//
	protected List<BigDecimal> queryForBigDecimals(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getBigDecimal(1), parameters);
	}
	//
	protected List<Byte> queryForBytes(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getByte(1), parameters);
	}
	//
	protected List<Date> queryForDates(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getTimestamp(1), parameters);
	}
	//
	public String getDatasourceIndex() {
		return datasourceIndex;
	}
	//
	public void setDatasourceIndex(String datasourceIndex) {
		this.datasourceIndex = datasourceIndex;
	}
	//
	public DatabaseType getDatabaseType() {
		return getSmartDataSource().getDatabaseType();
	}
	//
	public SmartDataSource getSmartDataSource() {
		return SmartJdbc.getDatasource(datasourceIndex);
	}
	//
	public Connection getConnection() {
		return getSmartDataSource().getConnection();
	}
	//
	public List<SqlInterceptor> getSqlInterceptors() {
		return getSmartDataSource().getSqlInterceptors();
	}
	//
	protected void set(Connection conn,
			PreparedStatement ps, 
			Object... objs)
			throws SQLException {
		if (objs == null || objs.length == 0) {
			return;
		}
		int i = 1;
		for (Object o : objs) {
			if (o == null) {
				ps.setString(i++, null);
				continue;
			}
			Class<?> type=o.getClass();
			if (o instanceof String) {
				ps.setString(i++, ((String) o));
				continue;
			}
			if (o instanceof Date) {
				Date date = (Date) o;
				ps.setTimestamp(i++, new Timestamp(date.getTime()));
				continue;
			}
			// Integer
			if (o instanceof Integer) {
				ps.setInt(i++, ((Integer) o));
				continue;
			}
			if (o instanceof Double) {
				ps.setDouble(i++, ((Double) o));
				continue;
			}
			if (o instanceof Float) {
				ps.setFloat(i++, ((Float) o));
				continue;
			}
			if (o instanceof BigDecimal) {
				ps.setBigDecimal(i++, ((BigDecimal) o));
				continue;
			}
			if (o instanceof Long) {
				ps.setLong(i++, ((Long) o));
				continue;
			}
			if (o instanceof Short) {
				ps.setShort(i++, ((Short) o));
				continue;
			}
			if (o instanceof Byte) {
				ps.setByte(i++, ((Byte) o));
				continue;
			}
			if (o instanceof byte[]) {
				ps.setBytes(i++, ((byte[]) o));
				continue;
			}
			if (o instanceof Boolean) {
				ps.setBoolean(i++, ((Boolean) o));
				continue;
			}
			if (Types.ARRAY_TYPES.contains(type)) {
				if(type.equals(String[].class)) {
					ps.setArray(i++, conn.createArrayOf("TEXT",(Object[]) o));
				}
				if(type.equals(Short[].class)) {
					ps.setArray(i++, conn.createArrayOf("INTEGER",(Object[]) o));
				}
				if(type.equals(Integer[].class)) {
					ps.setArray(i++, conn.createArrayOf("INTEGER",(Object[]) o));
				}
				if(type.equals(Long[].class)) {
					ps.setArray(i++, conn.createArrayOf("BIGINT",(Object[]) o));
				}
				if(type.equals(Float[].class)) {
					ps.setArray(i++, conn.createArrayOf("FLOAT",(Object[]) o));
				}
				if(type.equals(Double[].class)) {
					ps.setArray(i++, conn.createArrayOf("DOUBLE",(Object[]) o));
				}
				continue;
			}
			throw new IllegalArgumentException("unsupport type:" + o.getClass());
		}
	}
}
